package dao;

import entity.Users;
import utils.ConnectionUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @param
 * @return
 */
public class UsersDao {
    //添加用户
    public void addUsers(Users users) throws SQLException {
        //1.获取连接
        Connection connection = ConnectionUtils.getConnection();
        //2.书写你要发送给仓库的命令
        String sql = "insert into tb_users(username,password,job,introduction) values(?,?,?,?)";
        //3.通过连接构建一个载体，这个载体就是加载命令的
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //4.填充问号
        preparedStatement.setString(1, users.getUsername());
        preparedStatement.setString(2, users.getPassword());
        preparedStatement.setString(3, users.getJob());
        preparedStatement.setString(4, users.getIntroduction());
        //5.启动载体，执行sql语句
        preparedStatement.execute();
    }

    //修改用户
    public void updateUsers(int id, Users users) throws SQLException {
        //1.获取连接
        Connection connection = ConnectionUtils.getConnection();
        //2.书写你要发送给仓库的命令
        String sql = "UPDATE tb_users set username=?,`password`=? WHERE id=?";
        //3.通过连接构建一个载体，这个载体就是加载命令的
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //4.填充问号
        preparedStatement.setString(1, users.getUsername());
        preparedStatement.setString(2, users.getPassword());
        preparedStatement.setInt(3, id);
        //5.启动载体，执行sql语句
        preparedStatement.execute();
    }

    //删除用户
    public void deleteUsers(int id) throws SQLException {
        //1.获取连接
        Connection connection = ConnectionUtils.getConnection();
        //2.书写你要发送给仓库的命令
        String sql = "DELETE from tb_users WHERE id=?";
        //3.通过连接构建一个载体，这个载体就是加载命令的
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //4.填充问号
        preparedStatement.setInt(1, id);
        //5.启动载体，执行sql语句
        preparedStatement.execute();
    }

    //查询所有
    public List<Users> findAll() throws SQLException {
        //1.创建一个用来放入数据的集合
        List<Users> users = new ArrayList<>();
        Connection connection = ConnectionUtils.getConnection();
        //2.书写你要发送给仓库的命令
        String sql = "select id,username,password,job,introduction from tb_users";
        //3.通过连接构建一个载体，这个载体就是加载命令的
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            Users u = new Users();
            u.setId(resultSet.getInt(1));
            u.setUsername(resultSet.getString(2));
            u.setPassword(resultSet.getString(3));
            u.setJob(resultSet.getString(4));
            u.setIntroduction(resultSet.getString(5));
            users.add(u);
        }
        return users;
    }

    //查询单个用户
    public Users findUser(String username) throws SQLException {
        //1.创建一个用来放入数据的集合
        Users users = new Users();
        Connection connection = ConnectionUtils.getConnection();

        //2.书写你要发送给仓库的命令
        String sql = "select id,username,password,job,introduction from tb_users where username=?";
        //3.通过连接构建一个载体，这个载体就是加载命令的
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, username);

        ResultSet resultSet = preparedStatement.executeQuery();
        if(resultSet.next()){
            users.setId(resultSet.getInt(1));
            users.setUsername(resultSet.getString(2));
            users.setPassword(resultSet.getString(3));
            users.setJob(resultSet.getString(4));
            users.setIntroduction(resultSet.getString(5));
        }
        return users;
    }


    public boolean login(Users users) throws SQLException {
        //1、获取连接
        Connection connection = ConnectionUtils.getConnection();
        //2、书写你要发送给仓库的命令
        String sql = "select * from tb_users where username=? and password=?";
        //3、通过连接构建一个载体，这个载体就是用来加载这个命令的
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //4、如果有问号，进行问号的填充
        preparedStatement.setString(1, users.getUsername());
        preparedStatement.setString(2, users.getPassword());
        //5、执行载体，因为是查询，所以返回一个结果集对象-->ResultSet
        ResultSet resultSet = preparedStatement.executeQuery();
        //6、对结果集来进行判断,如果有数据，就证明登录成功，返回true
        if (resultSet.next()) {
            return true;
        }
        //7、如果前面没返回true，意味着用户名密码错误，这里返回false
        return false;
    }


    public boolean checkUsername(String username) throws SQLException {
        //1、获取连接
        Connection connection = ConnectionUtils.getConnection();
        //2、书写你要发送给仓库的命令
        String sql = "select * from tb_users where username=?";
        //3、通过连接构建一个载体，这个载体就是用来加载这个命令的
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //4、如果有问号，进行问号的填充
        preparedStatement.setString(1, username);
        //5、执行载体，因为是查询，所以返回一个结果集对象-->ResultSet
        ResultSet resultSet = preparedStatement.executeQuery();
        //6、对结果集来进行判断,如果有数据，代表用户名不能使用
        if (resultSet.next()) {
            return false;
        }
        //7、如果前面没返回false，意味着用户名可以使用，这里返回true
        return true;
    }

}
